<?php

	class Rekap extends arfaController
	{
		public function __construct()
		{
			parent::__construct();
			$this->load->model("master_periode_model");
			$this->load->model("ruas_jalan_model");
		}
		
		public function index()
		{
			$data["judul"]			= "Ruas Jalan";
			$data["post_action"]	= "rekap/ekspor";
			$data["data_periode"]	= $this->master_periode_model->get();
			$data["content"]	= $this->load->view("v_rekap",$data,true);
			$this->load->view("main",$data);
		}
		
		public function ekspor()
		{
			$this->form_validation->set_rules("periode","Periode","required");
			
			if($this->form_validation->run() == FALSE)
			{
				echo "<script>alert('Pilih periode terlebih dahulu !!!')</script>";
				echo "<script>document.location='index'</script>";
				echo "salah";
			}
			else
			{
				$this->load->library('PHPExcel');
				$this->load->library('PHPExcel/IOFactory');
				$periode	= $this->input->post("periode");
				$data		= $this->ruas_jalan_model->cetak_rekap($periode);
				$data_r		= $this->ruas_jalan_model->cetak_rekap_total($periode);
				
				//membuat PHPexcel baru
				$objPHPExcel = new PHPExcel();
				
				/** Sheet Kondisi Jalan **/
				
				//pengaturan titile sheet pada excel
				$objPHPExcel->getSheet(0)->setTitle('Data Kondisi Jalan');
				$objPHPExcel->getProperties()->setTitle("title")->setDescription("description");
				
				//pengaturan sheet aktif
				$objPHPExcel->setActiveSheetIndex(0);
				
				//merge cell pada excel
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B2:S2');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B3:S3');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B6:F6');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G6:K6');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('L6:P6');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('Q6:Q8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('R6:R8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('S6:S8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('B7:D9');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('E7:E9');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('F7:F9');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G7:G8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('H7:H8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('I7:I8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('J7:K7');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('L7:L8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('M7:M8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('N7:N8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('O7:P7');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('G9:U9');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('T6:T8');
				$objPHPExcel->setActiveSheetIndex(0)->mergeCells('U6:U8');
				
				//pengaturan auto size cell pada excel
				// foreach(range('B','S') as $col) 
				// {
					// $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
				// }
				
				//set lebar cell manual
				$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(8);
				$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(4);
				$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(3);
				$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(37);
				$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(11);
				$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(11);
				$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(11);
				$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(11);
				$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
				$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
				$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(11);
				$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(11);
				$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(11);
				$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(10);
				$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(10);
				$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(12);
				$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(12);
				$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(12);
				$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(12);
				$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(12);
				
				//pengaturan aligment HORIZONTAL dan VERTICAL ke CENTER
				$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
				
				//set wrap cell
				$objPHPExcel->getActiveSheet()->getStyle('E7:E'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
				$objPHPExcel->getActiveSheet()->getStyle('F7:F'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
				$objPHPExcel->getActiveSheet()->getStyle('G7:G'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
				$objPHPExcel->getActiveSheet()->getStyle('L7:L'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
				
				//Set warna cell
				$objPHPExcel->setActiveSheetIndex(0)->getStyle('G7')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'D9D9D9'))));
				$objPHPExcel->setActiveSheetIndex(0)->getStyle('L7')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'B7DEE8'))));
				$objPHPExcel->setActiveSheetIndex(0)->getStyle('Q6')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'F2DCDB'))));
				$objPHPExcel->setActiveSheetIndex(0)->getStyle('R6')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'E26B0A'))));
				$objPHPExcel->setActiveSheetIndex(0)->getStyle('S6')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'C4D79B'))));
				
				//Set border pada cell
				foreach(range(6,9) as $row)
				{
					foreach(range('B','U') as $col) 
					{				  
						$cell = $col.$row;
						$objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					}
				}
			
				//Set size font cell
				$objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setSize(18);
				$objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setSize(18);
				
				//Set boldfont cell
				$objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet()->getStyle('Q4')->getFont()->setBold(true);
				
				//Set title
				$objPHPExcel->getActiveSheet()->setCellValue('B2', "KONDISI PERKERASAN JALAN PROPINSI");	
				$objPHPExcel->getActiveSheet()->setCellValue('B3', "PER KABUPATEN / KOTA DI PROVINSI  MALUKU");
				$objPHPExcel->getActiveSheet()->setCellValue('B6', "UMUM");
				$objPHPExcel->getActiveSheet()->setCellValue('G6', "HOTMIX");
				$objPHPExcel->getActiveSheet()->setCellValue('L6', "LAPEN");
				$objPHPExcel->getActiveSheet()->setCellValue('Q6', "LPC");
				$objPHPExcel->getActiveSheet()->setCellValue('R6', "TANAH");
				$objPHPExcel->getActiveSheet()->setCellValue('S6', "HUTAN");
				$objPHPExcel->getActiveSheet()->setCellValue('B7', "No. Ruas");
				$objPHPExcel->getActiveSheet()->setCellValue('E7', "Nama Ruas");
				$objPHPExcel->getActiveSheet()->setCellValue('F7', "Pjg (km)");
				$objPHPExcel->getActiveSheet()->setCellValue('G7', "JMLH HOTMIX");
				$objPHPExcel->getActiveSheet()->setCellValue('H7', "BAIK");
				$objPHPExcel->getActiveSheet()->setCellValue('I7', "SEDANG");
				$objPHPExcel->getActiveSheet()->setCellValue('J7', "RSK");
				$objPHPExcel->getActiveSheet()->setCellValue('J8', "RR");
				$objPHPExcel->getActiveSheet()->setCellValue('K8', "RB");
				$objPHPExcel->getActiveSheet()->setCellValue('L7', "JMLH LAPEN");
				$objPHPExcel->getActiveSheet()->setCellValue('M7', "BAIK");
				$objPHPExcel->getActiveSheet()->setCellValue('N7', "SDG");
				$objPHPExcel->getActiveSheet()->setCellValue('O7', "RSK");
				$objPHPExcel->getActiveSheet()->setCellValue('O8', "RR");
				$objPHPExcel->getActiveSheet()->setCellValue('P8', "RB");
				$objPHPExcel->getActiveSheet()->setCellValue('G9', "(km)");
				$objPHPExcel->getActiveSheet()->setCellValue('T6', "RCI");
				$objPHPExcel->getActiveSheet()->setCellValue('U6', "IRI");
				
				$numb 		= 11;
				
				foreach($data as $row)
				{
					$jumlah_hotmix 	= $row["HOTMIX_BAIK"] + $row["HOTMIX_SEDANG"] + $row["HOTMIX_RR"] + $row["HOTMIX_RB"];
					$jumlah_lapen	= $row["LAPEN_BAIK"] + $row["LAPEN_SEDANG"] + $row["LAPEN_RR"] + $row["LAPEN_RB"];
					foreach(range('B','U') as $col) 
					{				  
						$cell = $col.$numb;
						$objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet()->getStyle($cell)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					}
					
					$objPHPExcel->setActiveSheetIndex(0)->getStyle('G'.$numb)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'D9D9D9'))));
					$objPHPExcel->setActiveSheetIndex(0)->getStyle('L'.$numb)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'B7DEE8'))));
					$objPHPExcel->setActiveSheetIndex(0)->getStyle('Q'.$numb)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'F2DCDB'))));
					$objPHPExcel->setActiveSheetIndex(0)->getStyle('R'.$numb)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'E26B0A'))));
					$objPHPExcel->setActiveSheetIndex(0)->getStyle('S'.$numb)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'C4D79B'))));
					
					$objPHPExcel->getActiveSheet()->setCellValue('A'.$numb, $row["NAMA_KOTA"]);
					$objPHPExcel->getActiveSheet()->setCellValue('B'.$numb, $row["NO_RUAS1"]);
					$objPHPExcel->getActiveSheet()->setCellValue('C'.$numb, $row["NO_RUAS2"]);
					$objPHPExcel->getActiveSheet()->setCellValue('D'.$numb, $row["NO_RUAS3"]);
					$objPHPExcel->getActiveSheet()->setCellValue('E'.$numb, $row["NAMA_RUAS"]);
					$objPHPExcel->getActiveSheet()->setCellValue('F'.$numb, $row["PANJANG"]);
					$objPHPExcel->getActiveSheet()->setCellValue('G'.$numb, $jumlah_hotmix);
					$objPHPExcel->getActiveSheet()->setCellValue('H'.$numb, $row["HOTMIX_BAIK"]);
					$objPHPExcel->getActiveSheet()->setCellValue('I'.$numb, $row["HOTMIX_SEDANG"]);
					$objPHPExcel->getActiveSheet()->setCellValue('J'.$numb, $row["HOTMIX_RR"]);
					$objPHPExcel->getActiveSheet()->setCellValue('K'.$numb, $row["HOTMIX_RB"]);
					$objPHPExcel->getActiveSheet()->setCellValue('L'.$numb, $jumlah_lapen);
					$objPHPExcel->getActiveSheet()->setCellValue('M'.$numb, $row["LAPEN_BAIK"]);
					$objPHPExcel->getActiveSheet()->setCellValue('N'.$numb, $row["LAPEN_SEDANG"]);
					$objPHPExcel->getActiveSheet()->setCellValue('O'.$numb, $row["LAPEN_RR"]);
					$objPHPExcel->getActiveSheet()->setCellValue('P'.$numb, $row["LAPEN_RB"]);
					$objPHPExcel->getActiveSheet()->setCellValue('Q'.$numb, $row["LPC"]);
					$objPHPExcel->getActiveSheet()->setCellValue('R'.$numb, $row["TANAH"]);
					$objPHPExcel->getActiveSheet()->setCellValue('S'.$numb, $row["HUTAN"]);
					$objPHPExcel->getActiveSheet()->setCellValue('T'.$numb, $row["RCI"]);
					$objPHPExcel->getActiveSheet()->setCellValue('U'.$numb, $row["IRI"]);
				
					$numb++;
				}
				
				/** Sheet Rekap Kondisi Jalan **/
				
				$objPHPExcel->createSheet(1);
				
				//pengaturan titile sheet pada excel
				$objPHPExcel->getSheet(1)->setTitle('Rekap Kondisi Jalan');
				$objPHPExcel->getProperties(1)->setTitle("title")->setDescription("description");
				
				//pengaturan sheet aktif
				$objPHPExcel->setActiveSheetIndex(1);
				
				//merge cell pada excel
				$objPHPExcel->setActiveSheetIndex(1)->mergeCells('B1:L1');
				$objPHPExcel->setActiveSheetIndex(1)->mergeCells('B2:L2');
				$objPHPExcel->setActiveSheetIndex(1)->mergeCells('B5:B7');
				$objPHPExcel->setActiveSheetIndex(1)->mergeCells('C5:C7');
				$objPHPExcel->setActiveSheetIndex(1)->mergeCells('D5:D6');
				$objPHPExcel->setActiveSheetIndex(1)->mergeCells('E5:H5');
				$objPHPExcel->setActiveSheetIndex(1)->mergeCells('I5:J5');
				$objPHPExcel->setActiveSheetIndex(1)->mergeCells('K5:L5');
				$objPHPExcel->setActiveSheetIndex(1)->mergeCells('D7:L7');
				
				//pengaturan auto size cell pada excel
				// foreach(range('B','S') as $col) 
				// {
					// $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
				// }
				
				//set lebar cell manual
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('B')->setWidth(8);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('C')->setWidth(28);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('D')->setWidth(9);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('E')->setWidth(8);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('F')->setWidth(8);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('G')->setWidth(8);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('H')->setWidth(8);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('I')->setWidth(8);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('J')->setWidth(8);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('K')->setWidth(8);
				$objPHPExcel->getActiveSheet(1)->getColumnDimension('L')->setWidth(8);
				
				//set tinggi cell manual
				$objPHPExcel->getActiveSheet(1)->getRowDimension('5')->setRowHeight(24);
				$objPHPExcel->getActiveSheet(1)->getRowDimension('6')->setRowHeight(31);
				
				//pengaturan aligment HORIZONTAL dan VERTICAL ke CENTER
				$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
				
				//set wrap cell
				$objPHPExcel->getActiveSheet(1)->getStyle('D5:D'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
				$objPHPExcel->getActiveSheet(1)->getStyle('J6:J'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
				$objPHPExcel->getActiveSheet(1)->getStyle('L6:L'.$objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); 
				
				//Set warna cell
				$objPHPExcel->setActiveSheetIndex(1)->getStyle('D5')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'B7DEDF'))));
				$objPHPExcel->setActiveSheetIndex(1)->getStyle('E5')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'FABF8F'))));
				$objPHPExcel->setActiveSheetIndex(1)->getStyle('E6:H6')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'FABF8F'))));
				$objPHPExcel->setActiveSheetIndex(1)->getStyle('I5')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => '92D050'))));
				$objPHPExcel->setActiveSheetIndex(1)->getStyle('I6:J6')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => '92D050'))));
				$objPHPExcel->setActiveSheetIndex(1)->getStyle('K5')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'C4BD97'))));
				$objPHPExcel->setActiveSheetIndex(1)->getStyle('K6:L6')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'C4BD97'))));
				
				//Set border pada cell
				foreach(range(5,7) as $row)
				{
					foreach(range('B','L') as $col) 
					{				  
						$cell = $col.$row;
						$objPHPExcel->getActiveSheet(1)->getStyle($cell)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet(1)->getStyle($cell)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet(1)->getStyle($cell)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet(1)->getStyle($cell)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					}
				}
			
				//Set size font cell
				$objPHPExcel->getActiveSheet(1)->getStyle('B1')->getFont()->setSize(16);
				$objPHPExcel->getActiveSheet(1)->getStyle('B2')->getFont()->setSize(16);
				
				//Set boldfont cell
				$objPHPExcel->getActiveSheet(1)->getStyle('B1')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet(1)->getStyle('B2')->getFont()->setBold(true);
				$objPHPExcel->getActiveSheet(1)->getStyle('B5:L7')->getFont()->setBold(true);
				
				//Set title
				$objPHPExcel->getActiveSheet(1)->setCellValue('B1', "REKAPITULASI KONDISI PERKERASAN");	
				$objPHPExcel->getActiveSheet(1)->setCellValue('B2', "JALAN PROVINSI PER KABUPATEN / KOTA DI PROVINSU MALUKU");
				$objPHPExcel->getActiveSheet(1)->setCellValue('B5', "No.");
				$objPHPExcel->getActiveSheet(1)->setCellValue('C5', "Kabupaten / Kota");
				$objPHPExcel->getActiveSheet(1)->setCellValue('D5', "Panjang Jalan");
				$objPHPExcel->getActiveSheet(1)->setCellValue('E5', "Kondisi");
				$objPHPExcel->getActiveSheet(1)->setCellValue('I5', "Perkerasan");
				$objPHPExcel->getActiveSheet(1)->setCellValue('K5', "Kemantapan");
				$objPHPExcel->getActiveSheet(1)->setCellValue('E6', "Baik");
				$objPHPExcel->getActiveSheet(1)->setCellValue('F6', "Sdg");
				$objPHPExcel->getActiveSheet(1)->setCellValue('G6', "R.R");
				$objPHPExcel->getActiveSheet(1)->setCellValue('H6', "R.B");
				$objPHPExcel->getActiveSheet(1)->setCellValue('I6', "Aspal");
				$objPHPExcel->getActiveSheet(1)->setCellValue('J6', "Non Aspal");
				$objPHPExcel->getActiveSheet(1)->setCellValue('K6', "Mantap");
				$objPHPExcel->getActiveSheet(1)->setCellValue('L6', "Tidak Mantap");
				$objPHPExcel->getActiveSheet(1)->setCellValue('D7', "(Km)");
				
				$numb 		= 8;
				$no			= 1;
				foreach($data_r as $row)
				{
					foreach(range('B','L') as $col) 
					{				  
						$cell = $col.$numb;
						$objPHPExcel->getActiveSheet(1)->getStyle($cell)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet(1)->getStyle($cell)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet(1)->getStyle($cell)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
						$objPHPExcel->getActiveSheet(1)->getStyle($cell)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
					}
					
					$objPHPExcel->setActiveSheetIndex(1)->getStyle('D'.$numb)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'B7DEDF'))));
					$objPHPExcel->setActiveSheetIndex(1)->getStyle('E'.$numb.':H'.$numb)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'FABF8F'))));
					$objPHPExcel->setActiveSheetIndex(1)->getStyle('I'.$numb.':J'.$numb)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => '92D050'))));
					$objPHPExcel->setActiveSheetIndex(1)->getStyle('K'.$numb.':L'.$numb)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'C4BD97'))));
					
					$objPHPExcel->getActiveSheet()->setCellValue('B'.$numb, $no);
					$objPHPExcel->getActiveSheet()->setCellValue('C'.$numb, $row["NAMA_KOTA"]);
					$objPHPExcel->getActiveSheet()->setCellValue('D'.$numb, $row["TOTAL_JALAN"]);
					$objPHPExcel->getActiveSheet()->setCellValue('E'.$numb, $row["KONDISI_BAIK"]);
					$objPHPExcel->getActiveSheet()->setCellValue('F'.$numb, $row["KONDISI_SDG"]);
					$objPHPExcel->getActiveSheet()->setCellValue('G'.$numb, $row["KONDISI_RR"]);
					$objPHPExcel->getActiveSheet()->setCellValue('H'.$numb, $row["KONDISI_RB"]);
					$objPHPExcel->getActiveSheet()->setCellValue('I'.$numb, $row["ASPAL"]);
					$objPHPExcel->getActiveSheet()->setCellValue('J'.$numb, $row["NON_ASPAL"]);
					$objPHPExcel->getActiveSheet()->setCellValue('K'.$numb, $row["MANTAP"]);
					$objPHPExcel->getActiveSheet()->setCellValue('L'.$numb, $row["TIDAK_MANTAP"]);
				
					$numb++;
					$no++;
				}
				
				$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
				header('Content-Type: application/vnd.ms-excel');
				header('Content-Disposition: attachment;filename="Data Rekap Ruas Jalan.xls"');
				header('Cache-Control: max-age=0');
				$objWriter->save('php://output');
			}
		}
	}